﻿using com.nm;
using com.nm.db.Helper;
using com.nm.library.Helper;
using FluentScheduler;
using InventoryToPredictAPI.model;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace InventoryToPredictAPI.Util
{
    public class ReturPlanJob : IJob
    {
        /// <summary>
        /// 同步总条数
        /// </summary>
        private int SyncCount = 0;
        public void Execute()
        {
            try
            {
                Log.WriteLog($"定时执行任务：START", "RegistryFluentScheduler ReturPlanJob Execute", Log.LogType.INFO);
                //同步业务
                SyncVerticaByErp();
                Log.WriteLog($"定时执行任务：END", "RegistryFluentScheduler ReturPlanJob Execute", Log.LogType.INFO);
            }
            catch (Exception ex)
            {
                Log.WriteLog($"定时执行任务：ERROR {Environment.NewLine + ex.Message} { Environment.NewLine + ex.Source} {Environment.NewLine + ex.StackTrace}", "RegistryFluentScheduler MyJob ", Log.LogType.ERROR);
            }
        }

        #region 回货计划同步==> 业务
        /// <summary>
        /// 
        /// </summary>
        private VerticaDataHelper VerticaHelper { get; set; } = new VerticaDataHelper();
        /// <summary>
        /// 回货计划ERP=>同步到Vertica
        /// </summary>
        public void SyncVerticaByErp()
        {
            #region 获取Erp数据
            string sql = string.Empty;
            sql = $@"SELECT ShortName,StockID,OrderBillNo,ManualBillNo,VendCustID,MaterialShortName,MaterialID,StartShipDate,cast(Qty as int)Qty,cast(RemainQty as int)RemainQty,KindName 
                         FROM HKERP_NOLIVE..KM_Pur_ReceiptPlan
                         ORDER BY StartShipDate,OrderBillNo,MaterialID DESC";
            var daset = new SQLErpDBHelper("HKERP_NOLIVE").Query_Task(sql);
            IList<ReturPlan.ReturPlanERPModel> list = new List<ReturPlan.ReturPlanERPModel>();
            if (daset != null && daset.Tables.Count > 0)
            {
                var dt = daset.Tables[0];
                list = ModelConvertHelper<ReturPlan.ReturPlanERPModel>.ConvertToModel(dt);
                SyncCount = list.Count;
                Log.WriteLog($"定时执行任务：获取ERP{SyncCount}条数据", "RegistryFluentScheduler ReturPlanJob SyncVerticaByErp", Log.LogType.INFO);
            }
            #endregion
            if (list != null && list.Count > 0)
            {
                //同步Vertica
                SyncVerticaByErpList(list);
                
                Log.WriteLog($"定时执行任务：同步Vertica{SyncCount}条数据", "RegistryFluentScheduler ReturPlanJob SyncVerticaByErp", Log.LogType.INFO);
            }
        }

        /// <summary>
        /// 同步数据并计算回货状态
        /// </summary>
        /// <param name="erplist"></param>
        private void SyncVerticaByErpList(IList<ReturPlan.ReturPlanERPModel> erplist)
        {
            #region 计算更新或者增加SQL
            StringBuilder syncVerticaSql = new StringBuilder();
            syncVerticaSql.Append("update ndw.ndw_return_plan set ChangeFlag = 3;");
            foreach (var erpModel in erplist)
            {
                var dbModel = GetReturPlanDBModel(erpModel.OrderBillNo, erpModel.MaterialID);
                if (dbModel != null)
                {
                    syncVerticaSql.Append(UpdateDBModelByErp(dbModel, erpModel));
                }
                else
                {
                    syncVerticaSql.Append(AddDBModelByErp(erpModel));
                }
            }
            string sql = syncVerticaSql.ToString();
            if (!string.IsNullOrEmpty(sql))
            {
                int count = VerticaHelper.GetNonQuery(sql);
                bool ex_result = count > 0;
                if (!ex_result)
                {
                    Log.Error(new Exception($"《回货计划》UpdateDBModelByErp同步异常：{sql}"));
                }
            }
            #endregion

            #region 计算超时SQL
            //string updSql = SyncVerticaDateTime();
            //if (!string.IsNullOrEmpty(updSql))
            //{
            //    int count = VerticaHelper.GetNonQuery(sql);
            //    bool ex_result = count > 0;
            //    if (!ex_result)
            //    {
            //        Log.Error(new Exception($"《回货计划》UpdateDBModelByErp同步异常：{updSql}"));
            //    }
            //}
            #endregion
        }

        /// <summary>
        /// 获取单个数据模型
        /// </summary>
        /// <param name="orderBillNo">订单号</param>
        /// <param name="materialID">货号</param>
        /// <returns></returns>
        private ReturPlan.ReturPlanDBModel GetReturPlanDBModel(string orderBillNo, string materialID)
        {
            ReturPlan.ReturPlanDBModel result = null;
            string sql = $@"select * from ndw.ndw_return_plan where OrderBillNo = '{orderBillNo}' and MaterialID = '{materialID}'";
            var dt = VerticaHelper.GetDataTable(sql);
            result = ModelConvertHelper<ReturPlan.ReturPlanDBModel>.ConvertToModel(dt).FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 更新数据(回货计划)
        /// </summary>
        /// <param name="erpModel"></param>
        /// <returns></returns>
        private string UpdateDBModelByErp(ReturPlan.ReturPlanDBModel dBModel, ReturPlan.ReturPlanERPModel erpModel)
        {
            string setSql = "";
            int ChangeFlag = 0;
            if (dBModel.RemainQty != erpModel.RemainQty)
            {
                ChangeFlag = 1;
                setSql += $",RemainQty = {erpModel.RemainQty}";
            }
            if (dBModel.Qty != erpModel.Qty)
            {
                ChangeFlag = 1;
                setSql += $",Qty={erpModel.Qty}";
            }
            if (DateTime.Compare(dBModel.StartShipDate.Value, DateTime.Now) < 0)
            {
                ChangeFlag = 2;
            }
            setSql += $",ChangeFlag = {ChangeFlag}";
            string result_sql = $"UPDATE ndw.ndw_return_plan set UpdateNum = (UpdateNum+1),EtlDate=now() {setSql}  where OrderBillNo = '{erpModel.OrderBillNo}' and MaterialID = '{erpModel.MaterialID}';";
            return result_sql;
        }

        /// <summary>
        /// 增加数据
        /// </summary>
        /// <param name="erpModel"></param>
        /// <returns></returns>
        private string AddDBModelByErp(ReturPlan.ReturPlanERPModel erpModel)
        {
            string extrSource = "NM";
            string companyID = "NM";
            var nowDate = DateTime.Now;
            var temp_date = Convert.ToDateTime(erpModel.StartShipDate);
            int ChangeFlag = 0;
            if (DateTime.Compare(temp_date, nowDate) < 0)
            {
                ChangeFlag = 2;
            }
            string sql = $@"INSERT INTO ndw.ndw_return_plan
                            (ExtrSource, CompanyID, VendCustID, ShortName, OrderBillNo,StockID,ManualBillNo, MaterialShortName, MaterialID, StartShipDate, Qty,RemainQty,ChangeFlag, KindName, UpdateNum, LoadTime)
                            VALUES('{extrSource}', '{companyID}', '{erpModel.VendCustID}', '{erpModel.ShortName}', '{erpModel.OrderBillNo}','{erpModel.StockID}', '{erpModel.ManualBillNo}', '{erpModel.MaterialShortName}', '{erpModel.MaterialID}', '{erpModel.StartShipDate}',{erpModel.Qty}, {erpModel.RemainQty}, {ChangeFlag},'{erpModel.KindName}', 0, now()); ";
            return sql;
        }

        /// <summary>
        /// 获取所有的回货计划列表
        /// </summary>
        /// <returns></returns>
        private IList<ReturPlan.ReturPlanVOMOdel> GetReturPlanListALL(int ChangeFlag = 0)
        {
            string extrSource = "NM";
            string companyId = "NM";
            string sql = $@"select ShortName,a.StockID,b.StockName,OrderBillNo,MaterialShortName,MaterialID,StartShipDate,RemainQty,AdjustStartShipDate,AdjustRemainQty,a.ChangeFlag
                     from ndw.ndw_return_plan a INNER join nods.nods_erp_Bas_Stock b on a.StockID = b.StockID 
                     where a.ExtrSource = '{extrSource}' and a.CompanyID = '{companyId}' and a.ChangeFlag <={ChangeFlag}
                     ORDER BY StartShipDate,OrderBillNo,MaterialID DESC";
            var dt = VerticaHelper.GetDataTable(sql);
            var list = ModelConvertHelper<ReturPlan.ReturPlanVOMOdel>.ConvertToModel(dt);
            return list;
        }

        /// <summary>
        /// 计算回货计划完成或者超时SQL
        /// </summary>
        /// <returns></returns>
        private string SyncVerticaDateTime()
        {
            StringBuilder sqlBui = new StringBuilder();
            var list = GetReturPlanListALL(1);
            DateTime nowDate = DateTime.Now;
            foreach (var item in list)
            {
                if (item.StartShipDate.HasValue)
                {
                    if (DateTime.Compare(item.StartShipDate.Value, nowDate) < 0)
                    {
                        if (item.AdjustStartShipDate.HasValue)
                        {
                            if (DateTime.Compare(item.AdjustStartShipDate.Value, nowDate) < 0)
                            {
                                //更新回货计划超时状态
                                sqlBui.Append($"update ndw.ndw_return_plan set ChangeFlag = 2 where OrderBillNo = '{item.OrderBillNo}' and MaterialID = '{item.MaterialID}';");
                            }
                        }
                        else
                        {
                            //更新回货计划超时状态
                            sqlBui.Append($"update ndw.ndw_return_plan set ChangeFlag = 2 where OrderBillNo = '{item.OrderBillNo}' and MaterialID = '{item.MaterialID}';");
                        }
                    }
                }
            }
            return sqlBui.ToString();
        }

        #endregion

    }
}
